# app.py
from flask import Flask, render_template
import pymysql
from pymysql.cursors import DictCursor

app = Flask(__name__)

@app.route('/')
def index():
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='041023',
        database='student_statistics',
        charset='utf8mb4',
        cursorclass=DictCursor
    )
    try:
        with connection.cursor() as cursor:
            sql = """
                SELECT semester, class_id, gender, SUM(count) AS count
                FROM student_stats
                GROUP BY semester, class_id, gender
                ORDER BY semester, class_id, gender;
            """
            cursor.execute(sql)
            results = cursor.fetchall()
            for row in results:
                print(row)  # 打印每一行数据，用于检查
            # 处理数据，格式化为 ECharts 需要的格式
            data = []
            for row in results:
                data.append({
                    "semester": row['semester'],
                    "class_id": row['class_id'],
                    "gender": '男' if row['gender'] == '0' else '女',
                    "count": row['count']
                })

            return render_template('index.html', data=data)
    finally:
        connection.close()

if __name__ == '__main__':
    app.run(debug=True)
